MySQL-5.7 Insert语句详解
1.语法
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
{VALUES | VALUE} (value_list) [, (value_list)] ...
[ON DUPLICATE KEY UPDATE assignment_list]
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
SET assignment_list
[ON DUPLICATE KEY UPDATE assignment_list]
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
SELECT ...
[ON DUPLICATE KEY UPDATE assignment_list]
value:
{expr | DEFAULT}
value_list:
value [, value] ...
assignment:
col_name = value
assignment_list:
assignment [, assignment] ...
2.三种常用语法示例
mysql> desc students;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(54) | YES | | NULL | |
| gender | varchar(12) | YES | | NULL | |
| dept_id | int(11) | NO | MUL | NULL | |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> select * from students;
+-----+-------+--------+---------+
| sid | sname | gender | dept_id |
+-----+-------+--------+---------+
| 1 | aaa | 1 | 2 |
| 2 | bbb | 2 | 2 |
| 3 | ccc | 3 | 2 |
+-----+-------+--------+---------+
3 rows in set (0.00 sec)
mysql> insert into students values(4,'aaaa',1,1);
Query OK, 1 row affected (0.04 sec)
mysql> insert into students set sid=5,sname='ddd',gender=2,dept_id=1;
Query OK, 1 row affected (0.03 sec)
mysql> insert into students select * from students_bak;
说明：
insert...values和insert...set两种语句都是将指定的数据插入到现成的表中，而insert....select语句是将另外表中的数据查出来并插入到现成的表中；
3.属性说明
1）Partition
代表可以将数据插入到指定的表分区中
2）Tbl_name
代表将数据插入到目标表
3）Col_name
代表要插入指定数据的目标列，如果是多列则用逗号隔开，如果目标表中的某些列没有在Insert语句中指定，则这些列会插入默认值，当然可以使用default显示指定插入默认值
4）LOW_PRIORITY
INSERT的执行被推迟到没有其他用户正在读取表。在这种情况下，用户必须等到插入语句完成后，如果表频繁使用，它可能花很长时间。这与INSERT DELAYED让用户马上继续正好相反。
LOW_PRIORITY和HIGH_PRIORITY仅在MyISAM，MEMORY和MERGE三种存储引擎下才生效。
5）IGNORE
表中任何复制一个现有PRIMARY或UNIQUE键的行被忽略并且不被插入。如果你不指定IGNORE，插入如果有任何复制现有关键值的行被放弃。
当违反主键和唯一键约束条件时不报错只产生警告信息，违反的行被丢弃，而不是整个语句回退；在数据类型转换有问题时也是如此。
6）DELAYED（5.7版本不再支持，语句不会报错，但会产生警告）
如果用户不能等到INSERT完成，它是很有用的。当你为日志登录使用MySQL时，而且你也周期性地运行花很长时间完成的SELECT语句，这是一个常见的问题。
当你使用INSERT DELAYED时，用户将马上准备好，并且当表不被任何其他的线程使用时，行将被插入。
另一个使用INSERT DELAYED的主要好处是从很多用户插入被捆绑在一起并且写进一个块。这比做很多单独的插入要来的快。
使用DELAYED时有一些限制：
- INSERT DELAYED仅适用于MyISAM, MEMORY和ARCHIVE表。对于MyISAM表，如果在数据文件的中间没有空闲的块，则支持同时采用SELECT和INSERT语句。在这些情况下，基本不需要对MyISAM使用INSERT DELAYED。
- INSERT DELAYED应该仅用于指定值清单的INSERT语句。服务器忽略用于INSERT DELAYED...SELECT语句的DELAYED和INSERT DELAYED...ON DUPLICATE UPDATE语句的DELAYED。
- 因为在行被插入前，语句立刻返回，所以您不能使用LAST_INSERT_ID()来获取AUTO_INCREMENT值。AUTO_INCREMENT值可能由语句生成。
- 对于SELECT语句，DELAYED行不可见，直到这些行确实被插入了为止。
- DELAYED在从属复制服务器中被忽略了，因为DELAYED不会在从属服务器中产生与主服务器不一样的数据。
7）ON DUPLICATE KEY UPDATE
如果您指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE。
当您使用ON DUPLICATE KEY UPDATE时，DELAYED选项被忽略。
--假设a,b为唯一索引,表table没有1,2这样的行是正常插入数据，冲突时，更新c列的值
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=3;
--或者是
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=values(c);
--引用其他列更新冲突的行
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
向一个已定义为NOT NULL的列中插入NULL。对于一个多行INSERT语句或INSERT INTO...SELECT语句，根据列数据的类型，列被设置为隐含的默认值。对于数字类型，默认值为0；对于字符串类型，默认值为空字符串('')；对于日期和时间类型，默认值为“zero”值。
INSERT INTO...SELECT的ON DUPLICATE KEY UPDATE
insert into tbl_name1(a,b,c)
select col1,col2,col3 from tbl_name2
ON DUPLICATE KEY UPDATE c=values(c);
4.注意事项
1）Values中除了可以指定确定的数值之外，还可以使用表达式expr
INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2); 正确
INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15); 错误
2）执行结果
Insert...values和Insert...select语句执行结果如下：
Records:100 Duplicates:0 Warnings:0
Records代表语句操作了多少行数据，但不一定是多少行被插入的数据，因为如果存在相同行数据且违反了某个唯一性，Duplicates会显示非0数值；
warnings代表语句执行过程中的一些警告信息；
Warnings的一些触发情况：
- 插入NULL到被声明了NOT NULL的列，列被设置为它的缺省值。
- 将超出列范围的值设置给一个数字列，值被剪切为范围内适当的端点值。
- 将数字列设成例如'10.34 a'的值，拖尾的垃圾被剥去并仍然是数字部分被插入。如果值根本不是一个数字，列被设置到0。
- 把一个字符串插入到超过列的最大长度的一个CHAR、VARCHAR、TEXT或BLOB列中。值被截断为列的最大长度。
- 把一个对列类型不合法的值插入到一个日期或时间列。列被设置为该列类型适当的“零”值。
3）Insert...select
当目标表和select语句中的表相同时，则会将select语句的结果存放在临时表中，再插入到目标表中（注意执行顺序）。
4）INSERT INTO ... SELECT语句满足下列条件
- 查询不能包含一个ORDER BY子句。
- INSERT语句的目的表不能出现在SELECT查询部分的FROM子句，因为这在ANSI SQL中被禁止让从你正在插入的表中SELECT。（问题是SELECT将可能发现在同一个运行期间内先前被插入的记录。当使用子选择子句时，情况能很容易混淆）
- AUTO_INCREMENT列像往常一样工作。
